
# first an ODBC connection to MSSQL database server has to be setup
# http://www.time-travellers.org/shane/howtos/MS-SQL-Express-Python-HOWTO.html
#  section Create the ODBC Data Source

import dbi
import odbc
import sys
import time

dtnow_iso = lambda : time.strftime ("%Y-%m-%dT%H%M%S", time.localtime())
#2008-07-16T19:44:18

def main(odbc_conn_name):
    """ """

    #conn=odbc.odbc("DSN=dedus9009")
    conn=odbc.odbc("DSN=%s" % (odbc_conn_name))
    
    cursor=conn.cursor()
    
    cursor.execute("SELECT * FROM SYS.DATABASES")
    databases = cursor.fetchall()[:]
    
    o = open('%s_db_out_%s'  % (odbc_conn_name, dtnow_iso()), 'w')
    #o_err = open('%s_db_err' % (odbc_conn_name), 'w')
    o.write('DATABASE\tTABLE\tROWS_COUNT\tDATE_TIME\tQUERY_STATUS\tQUERY_ERROR\n')
    #o_err.write('DATABASE\tTABLE\tDESC_ERR\n')
    
    for dbx in databases:
    
        r = {'db': '', 'table':'', 'rows_count':''}
    
        r['db'] = dbx[0] # database name
        print r['db']
    
        if r['db'] in (      'master', 'model', 'backupmediaset'
                            , 'backupmediafamily''backupset', 'backupfile'
                            , 'restorehistory', 'restorefile', 'restorefilegroup'
                            , 'logmarkhistory', 'suspect_pages', 'msdb', 'tempdb'
                            ) :
            o.write("%s\t%s\t%s\t%s\t%s\t%s\n" % (r['db'], '', '', dtnow_iso(), 'skipped db', sys.exc_info()[:]))
        else:
            try:
                q = "USE %s" % (r['db'])
                cursor.execute(q)
                cursor.execute("""select * from information_schema.Tables""")
                tables = cursor.fetchall()[:]
                for t in tables:
                    r['tb'] = "%s.[%s]" % (t[1], t[2]) # table name
                    print r['tb']
                    try:
                        q = "SELECT count(*) FROM %s" % (r['tb'])
                        #print r['db'], q
                        cursor.execute(q)
                        tables_counts = cursor.fetchall()[:]
                    
                        for c in tables_counts:
                            r['rc'] = c[0] # rows count
                            o.write("%s\t%s\t%s\t%s\t%s\t%s\n" % (r['db'], r['tb'], r['rc'], dtnow_iso(), 'ok', 'none'))
                    except:
                        #print 'error:', r['tb']
                        o.write("%s\t%s\t%s\t%s\t%s\t%s\n" % (r['db'], r['tb'], r['rc'], dtnow_iso(), 'error', sys.exc_info()[:]))
            except:
                o.write("%s\t%s\t%s\t%s\t%s\t%s\n" % (r['db'], '', '', dtnow_iso(), 'error db', sys.exc_info()[:]))
    
    o.close()
    #o_err.close()

if __name__ == "__main__":
    ''' '''
    if  len(sys.argv) == 2:
        main(sys.argv[1])
    else:
        print """

        mssql_list.py <odbc_conn_name>

        example : mssql_list xxxx

        odbc_conn_name (mandatory)
            odbc connection name (database server to list)

        """
